package br.com.amadeus.android.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import br.com.amadeus.android.entities.Course;
import br.com.amadeus.android.entities.CourseUser;
import br.com.amadeus.android.entities.Resource;
import br.com.amadeus.android.util.Util;

public class CourseDAO {
	public static String[] columns = new String[] { CourseDAO.ID,
			CourseDAO.NAME, CourseDAO.BEGINDATE, CourseDAO.ENDDATE,
			CourseDAO.OBJECTIVES, CourseDAO.CONTENT, CourseDAO.ACTIVE,
			CourseDAO.AMADEUS_ID, CourseDAO.MAX_AMOUNT_STUDENTS,
			CourseDAO.NUMBER_OF_STUDENTS_IN_COURSE };

	public static final String ID = "_id";
	public static final String NAME = "name";
	public static final String BEGINDATE = "begin_date";
	public static final String ENDDATE = "end_date";
	public static final String OBJECTIVES = "objectives";
	public static final String CONTENT = "content";
	public static final String ACTIVE = "active";
	public static final String AMADEUS_ID = "amadeus_id";
	public static final String MAX_AMOUNT_STUDENTS = "max_amount_students";
	public static final String NUMBER_OF_STUDENTS_IN_COURSE = "number_students_course";

	private SQLiteDatabase db;

	private static CourseDAO instance = new CourseDAO();
	
	private List<Course> listInactiveCourses;

	public static CourseDAO getInstance(Context ctx) {

		if (instance.db == null || !instance.db.isOpen()) {
			instance.db = new DBHelper(ctx).getWritableDatabase();
		}
		return instance;
	}

	private CourseDAO() {
	}

	public long save(Course course) {
		long id = course.getId();
		if (id == 0) {
			return insert(course);
		} else {
			return update(course);
		}
	}

	private long insert(Course course) {
		long id_course = -1;

		db.beginTransaction();
		try {
			SimpleDateFormat dtFormat = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");
			ContentValues values = new ContentValues();
			values.put(CourseDAO.NAME, course.getName());
			values.put(CourseDAO.BEGINDATE,
					dtFormat.format(course.getBeginDate()));
			values.put(CourseDAO.ENDDATE, dtFormat.format(course.getEndDate()));
			values.put(CourseDAO.OBJECTIVES, course.getObjectives());
			values.put(CourseDAO.CONTENT, course.getContent());
			values.put(CourseDAO.ACTIVE, getStatusDate(course.getBeginDate(), course.getEndDate()));
			values.put(CourseDAO.AMADEUS_ID, course.getId_amadeus());
			values.put(CourseDAO.MAX_AMOUNT_STUDENTS, course.getMaxAmountStudents());
			values.put(CourseDAO.NUMBER_OF_STUDENTS_IN_COURSE, course.getNumberOfStudentsInCourse());

			id_course = db.insert(DBHelper.TABLE_COURSE, null, values);

			if (id_course != -1)
				db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}

		return id_course;
	}

	private String getStatusDate(Date beginDate, Date endDate) {
		Date dataAtual = new Date();
		if (beginDate.before(dataAtual) && endDate.after(dataAtual)) {
			return "S";
		}else{
			return "N";
		}
	}

	private long update(Course course) {
		boolean deuErro = false;
		db.beginTransaction();
		try {
			SimpleDateFormat dtFormat = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");
			ContentValues values = new ContentValues();
			values.put(CourseDAO.NAME, course.getName());
			values.put(CourseDAO.BEGINDATE,
					dtFormat.format(course.getBeginDate()));
			values.put(CourseDAO.ENDDATE, dtFormat.format(course.getEndDate()));
			values.put(CourseDAO.OBJECTIVES, course.getObjectives());
			values.put(CourseDAO.CONTENT, course.getContent());
			values.put(CourseDAO.ACTIVE, course.getActive());
			values.put(CourseDAO.AMADEUS_ID, course.getId_amadeus());
			values.put(CourseDAO.MAX_AMOUNT_STUDENTS, course.getMaxAmountStudents());
			values.put(CourseDAO.NUMBER_OF_STUDENTS_IN_COURSE, course.getNumberOfStudentsInCourse());

			deuErro = db.update(DBHelper.TABLE_COURSE, values, CourseDAO.ID
					+ " = ?", new String[] { String.valueOf(course.getId()) }) == 0;

			if (!deuErro)
				db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}
		return course.getId();
	}

	public void delete(int id) {
		db.beginTransaction();
		try {
			db.delete(DBHelper.TABLE_COURSE, CourseDAO.ID + "=?",
					new String[] { String.valueOf(id) });
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	public List<Course> getListActiveCourses(){
		this.listInactiveCourses = new ArrayList<Course>();
		List<Course> listActiveCourses = new ArrayList<Course>();
		List<Course> list = getListCourses();
		for (Course course : list) {
			if (course.getBeginDate().before(new Date()) && course.getEndDate().after(new Date())) {
				listActiveCourses.add(course);
			}else{
				listInactiveCourses.add(course);
			}
		}
		
		return listActiveCourses;
	}
	
	public List<Course> getListInactiveCourses(){
		return this.listInactiveCourses;
	}
	
	public List<Course> getListCourses(){
		List<Course> listCourses = new ArrayList<Course>();
		Cursor c = db.query(DBHelper.TABLE_COURSE, columns, null, null, null, null, null);
		if (c.moveToFirst()) {
							
			do {
				
				int id = c.getInt(c.getColumnIndex(CourseDAO.ID));
				String name = c.getString(c.getColumnIndex(CourseDAO.NAME));
				Date begindate = Util.getDate(c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)), Util.FORMATO_DATA_BD);
				Date enddate = Util.getDate(c.getString(c.getColumnIndex(CourseDAO.ENDDATE)), Util.FORMATO_DATA_BD);
				String objectives = c.getString(c.getColumnIndex(CourseDAO.OBJECTIVES));
				String content = c.getString(c.getColumnIndex(CourseDAO.CONTENT));
				String active = "";
				active = c.getString(c.getColumnIndex(CourseDAO.ACTIVE));
				int amadeus_id = c.getInt(c.getColumnIndex(CourseDAO.AMADEUS_ID));
				int maxStudents = c.getInt(c.getColumnIndex(CourseDAO.MAX_AMOUNT_STUDENTS));
				int numberStudents = c.getInt(c.getColumnIndex(CourseDAO.NUMBER_OF_STUDENTS_IN_COURSE));
				
				Course course = new Course(id, name, begindate, enddate, objectives, content, active, amadeus_id, maxStudents, numberStudents);
				listCourses.add(course);
				
			} while (c.moveToNext());
		}
		c.close();
		return listCourses;
	}

	public Course getCourse(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSE, null, CourseDAO.ID + "="
				+ id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourse(c);
		}
		c.close();
		return null;

	}
	
	public Course getCourseByAmadeusId(int amadeus_id){
		Cursor c = db.query(DBHelper.TABLE_COURSE, null, CourseDAO.AMADEUS_ID + "="
				+ amadeus_id, null, null, null, null);
		Course course = null;
		if (c.getCount() > 0) {
			c.moveToFirst();
			course = fillCourse(c);
		}
		c.close();
		return course;
	}

	protected static Course fillCourse(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseDAO.ID));
		String name = c.getString(c.getColumnIndex(CourseDAO.NAME));
		Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		String objectives = c.getString(c.getColumnIndex(CourseDAO.OBJECTIVES));
		String content = c.getString(c.getColumnIndex(CourseDAO.CONTENT));
		String active = "";
		active = c.getString(c.getColumnIndex(CourseDAO.ACTIVE));
		int idAmadeus = c.getInt(c.getColumnIndex(CourseDAO.AMADEUS_ID));
		int maxAmountStudents = c.getInt(c.getColumnIndex(CourseDAO.MAX_AMOUNT_STUDENTS));
		int numberOfStudentsInCourse = c.getInt(c.getColumnIndex(CourseDAO.NUMBER_OF_STUDENTS_IN_COURSE));
		c.close();

		return new Course(id, name, beginDate, endDate, objectives, content, active, idAmadeus, maxAmountStudents, numberOfStudentsInCourse);
	}

	public Course getCourseWithResourceList(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSE, null, CourseDAO.ID + "="
				+ id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourseWithResourceList(c);
		}
		return null;

	}

	protected Course fillCourseWithResourceList(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseDAO.ID));
		String name = c.getString(c.getColumnIndex(CourseDAO.NAME));
		Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		String objectives = c.getString(c.getColumnIndex(CourseDAO.OBJECTIVES));

		Course course = new Course(id, name, beginDate, endDate, objectives,
				new ArrayList<Resource>(), null);

		Cursor rc = db.query(DBHelper.TABLE_RESOURCE, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);

		rc.moveToFirst();

		while (!rc.isAfterLast()) {
			course.getResourceList().add(ResourceDAO.fillResource(rc));
			rc.moveToNext();
		}
		return course;
	}

	public Course getCourseWithCourseUserList(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSE, null, CourseDAO.ID + "="
				+ id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourse(c);
		}
		return null;

	}

	protected Course fillCourseWithCourseUserList(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseDAO.ID));
		String name = c.getString(c.getColumnIndex(CourseDAO.NAME));
		Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		String objectives = c.getString(c.getColumnIndex(CourseDAO.OBJECTIVES));

		Course course = new Course(id, name, beginDate, endDate, objectives,
				null, new ArrayList<CourseUser>());

		Cursor cuc = db.query(DBHelper.TABLE_COURSEUSER, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);

		cuc.moveToFirst();

		while (!cuc.isAfterLast()) {
			course.getCourseUserList().add(CourseUserDAO.fillCourseUser(cuc));
			cuc.moveToNext();
		}
		return course;
	}

	public Course getCourseFull(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSE, null, CourseDAO.ID + "="
				+ id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourse(c);
		}
		return null;

	}

	protected Course fillCourseFull(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseDAO.ID));
		String name = c.getString(c.getColumnIndex(CourseDAO.NAME));
		Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(CourseDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		String objectives = c.getString(c.getColumnIndex(CourseDAO.OBJECTIVES));

		Course course = new Course(id, name, beginDate, endDate, objectives,
				new ArrayList<Resource>(), new ArrayList<CourseUser>());

		Cursor cuc = db.query(DBHelper.TABLE_COURSEUSER, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);

		cuc.moveToFirst();

		while (!cuc.isAfterLast()) {
			course.getCourseUserList().add(CourseUserDAO.fillCourseUser(cuc));
			cuc.moveToNext();
		}

		Cursor rc = db.query(DBHelper.TABLE_RESOURCE, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);

		rc.moveToFirst();

		while (!rc.isAfterLast()) {
			course.getResourceList().add(ResourceDAO.fillResource(rc));
			rc.moveToNext();
		}
		return course;
	}

}
